package com.beordie.dao;

import com.beordie.model.Notice;
import com.beordie.model.Punishment;

import java.util.List;

/**
 * @Classname
 * @Description TODO
 * @Date 2021/7/13 17:16
 * @Created 30500
 */
public interface INoticeDao {
    String SQL_SELECT_ALL = "SELECT * FROM NOTICE";
    String SQL_SELECT_ALL_BY_LIMIT = "SELECT * FROM NOTICE LIMIT ? , ?";
    String SQL_INSERT_ONE = "INSERT INTO NOTICE VALUES(NULL,?,?,?,?,?,?,?,?)";
    String SQL_DELETE_BY_ID = "DELETE FROM NOTICE WHERE ID = ?";
    String SQL_UPDATE_BY_ID = "UPDATE NOTICE SET DAY = ?, TIME = ?, LOCATION = ?, RECORD = ?, DRIVERID = ?, POLICEID = ?, CARID = ? WHERE ID = ?";
    String SQL_SELECT_BY_NUMBER = "SELECT * FROM NOTICE WHERE NUMBER = ?";
    String SQL_SELECT_COUNT = "SELECT COUNT(1) FROM NOTICE";
    String SQL_SELECT_BY_KEY = "SELECT d.`name`,d.number,d.address,d.postcard,d.phone, " +
            " c.number cnumber,c.model,c.factory,c.createtime, " +
            " n.id,n.`day`,n.time,n.location,n.record," +
            " p.`name` pname,p.numberpolice" +
            " FROM notice n, car c, driver d, police p WHERE n.carid=c.id AND n.driverid=d.id AND n.policeid=p.id AND n.number = ?";
    String SQL_SELECT_BY_DRIVER = "SELECT d.`name`,d.number,d.address,d.postcard,d.phone, " +
            " c.number cnumber,c.model,c.factory,c.createtime, " +
            " n.id,n.`day`,n.time,n.location,n.record," +
            " p.`name` pname,p.numberpolice" +
            " FROM notice n, car c, driver d, police p WHERE n.carid=c.id AND n.driverid=d.id AND n.policeid=p.id AND d.number = ?";

    String SQL_SELECT_BY_CAR = "SELECT d.`name`,d.number,d.address,d.postcard,d.phone, " +
            " c.number cnumber,c.model,c.factory,c.createtime, " +
            " n.id,n.`day`,n.time,n.location,n.record," +
            " p.`name` pname,p.numberpolice" +
            " FROM notice n, car c, driver d, police p WHERE n.carid=c.id AND n.driverid=d.id AND n.policeid=p.id AND c.number = ?";
    String SQL_SELECT_BY_POLICE = "SELECT d.`name`,d.number,d.address,d.postcard,d.phone, " +
            " c.number cnumber,c.model,c.factory,c.createtime, " +
            " n.id,n.`day`,n.time,n.location,n.record," +
            " p.`name` pname,p.numberpolice" +
            " FROM notice n, car c, driver d, police p WHERE n.carid=c.id AND n.driverid=d.id AND n.policeid=p.id AND p.numberPolice = ?";
    public List<Notice> getAll();
    public List<Notice> getAllByLimit(boolean isLimit, int start, int size);
    public int insert(Notice notice);
    public int deleteById(int id);
    public int updateById(Notice notice, int id);
    public Notice findByNumber(String condition);
    public int getCount();
    public Notice getByKey(String number, String way);
}
